This project represents our inaugural analysis of four companies within the commercial sector over a three-year period, from 2021 to 2023, segmented by quarters. The database utilized in this analysis is entirely hypothetical and does not reflect actual figures or pertain to any real-world company.
Through comprehensive data analysis, we have extracted key performance indicators (KPIs) for each of the four companies. Based on these KPIs, we have formulated conclusions and developed strategic recommendations for our organization.
Scenario: I am a financial analyst at Tech Company, which operates in a commercial market comprising four companies. Our company has been experiencing a decline in both product sales and profits over time, unlike our competitors who are enjoying high profitability.
->NOTE: If you have any confusing just read Print Function in the at the Code it will be highly enough.
Below is the original hypothetical database used in this analysis:
library(opencpu)## Loading config from C:/Users/A.Dawod/AppData/Local/R/win-library/4.4/opencpu/config/defaults.conf## Loading config from C:\Users\A.Dawod\AppData\Roaming/R/config/R/opencpu/user.conf## Welcome to OpenCPU!library(readxl)
library(dplyr)##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionlibrary(tidyr)
options(tibble.print_max = 1000)
income_statment <- read_excel("D:/Project/Project_Four_Companies(Orginal).xlsx", sheet = 1)
balance_sheet_Assets <- read_excel("D:/Project/Project_Four_Companies(Orginal).xlsx", sheet = 2)
balance_sheet_L_S <- read_excel("D:/Project/Project_Four_Companies(Orginal).xlsx", sheet = 3)
excel_data<-bind_rows(income_statment,balance_sheet_Assets,balance_sheet_L_S)
### Display the data
print(income_statment)## # A tibble: 48 × 12
## Company Quarter Year Revenue COGS Gross_Profit Operating_Expenses EBIT
## <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Tech Q1 2021 648721. 365391. 283331. 189537. 93793.
## 2 Tech Q2 2021 565250. 260978. 304271. 127755. 176516.
## 3 Tech Q3 2021 758622. 454160. 304462. 172230. 132231.
## 4 Tech Q4 2021 669067. 350268. 318798. 143990. 174809.
## 5 Tech Q1 2022 881818. 429210. 452608. 256685. 195924.
## 6 Tech Q2 2022 518744. 278455. 240289. 121972. 118317.
## 7 Tech Q3 2022 656602. 344829. 311773. 133764. 178009.
## 8 Tech Q4 2022 922800. 489665. 433135. 250773. 182361.
## 9 Tech Q1 2023 639024. 348730. 290295. 178966. 111329.
## 10 Tech Q2 2023 595526. 336003. 259523. 150406. 109117.
## 11 Tech Q3 2023 869983. 390271. 479712. 212269. 267442.
## 12 Tech Q4 2023 731067. 436447. 294620. 212877. 81743.
## 13 Speed Q1 2021 657220. 333620. 323600. 186260. 137340.
## 14 Speed Q2 2021 980669. 576060. 404609. 241333. 163276.
## 15 Speed Q3 2021 551447. 314556. 236891. 163765. 73126.
## 16 Speed Q4 2021 787047. 347393. 439655. 234290. 205365.
## 17 Speed Q1 2022 641032. 308822. 332209. 171591. 160618.
## 18 Speed Q2 2022 563315. 310093. 253222. 156534. 96688.
## 19 Speed Q3 2022 994683. 502147. 492537. 286637. 205899.
## 20 Speed Q4 2022 658016. 344925. 313091. 142805. 170286.
## 21 Speed Q1 2023 574977. 330688. 244288. 132368. 111921.
## 22 Speed Q2 2023 677225. 282789. 394436. 139115. 255320.
## 23 Speed Q3 2023 900921. 386850. 514072. 216327. 297745.
## 24 Speed Q4 2023 520673. 230877. 289795. 117837. 171958.
## 25 Maclr Q1 2021 575230. 309856. 265373. 155277. 110097.
## 26 Maclr Q2 2021 588116. 331783. 256333. 134266. 122067.
## 27 Maclr Q3 2021 830948. 495346. 335602. 231040. 104562.
## 28 Maclr Q4 2021 610934. 327718. 283216. 174450. 108765.
## 29 Maclr Q1 2022 621120. 297236. 323884. 173750. 150134.
## 30 Maclr Q2 2022 844889. 459040. 385850. 226940. 158910.
## 31 Maclr Q3 2022 573364. 306862. 266502. 153783. 112719.
## 32 Maclr Q4 2022 849582. 352117. 497464. 218594. 278871.
## 33 Maclr Q1 2023 810913. 403150. 407763. 194734. 213029.
## 34 Maclr Q2 2023 634501. 256295. 378206. 137244. 240962.
## 35 Maclr Q3 2023 525940. 290613. 235327. 148695. 86632.
## 36 Maclr Q4 2023 723581. 348445. 375136. 157471. 217665.
## 37 Dain Q1 2021 582244. 316333. 265911. 174002. 91909.
## 38 Dain Q2 2021 600186. 334554. 265632. 122685. 142947.
## 39 Dain Q3 2021 950352. 556525. 393827. 272291. 121536.
## 40 Dain Q4 2021 970666. 471660. 499005. 243956. 255049.
## 41 Dain Q1 2022 512620. 214573. 298047. 145761. 152286.
## 42 Dain Q2 2022 562047. 268560. 293487. 136949. 156538.
## 43 Dain Q3 2022 690394. 402282. 288113. 147043. 141069.
## 44 Dain Q4 2022 892523. 534628. 357894. 209652. 148242.
## 45 Dain Q1 2023 786445. 346214. 440230. 219762. 220468.
## 46 Dain Q2 2023 916610. 455940. 460670. 195881. 264789.
## 47 Dain Q3 2023 744888. 362262. 382626. 166597. 216030.
## 48 Dain Q4 2023 883215. 404156. 479059. 225678. 253381.
## # ℹ 4 more variables: Interest_Expense <dbl>, EBT <dbl>, Tax_Expense <dbl>,
## # Net_Income <dbl>print(balance_sheet_Assets)## # A tibble: 48 × 9
## Company Quarter Year Cash Accounts_Receivable Inventory Current_Assets
## <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Tech Q1 2021 108280. 67301. 60333. 235914.
## 2 Tech Q2 2021 74653. 57080. 36565. 168298.
## 3 Tech Q3 2021 59313. 84926. 59168. 203407.
## 4 Tech Q4 2021 121790. 108084. 66411. 296284.
## 5 Tech Q1 2022 55973. 113650. 48630. 218253.
## 6 Tech Q2 2022 80702. 102817. 33238. 216757.
## 7 Tech Q3 2022 88388. 127710. 61840. 277938.
## 8 Tech Q4 2022 67934. 129025. 86907. 283866.
## 9 Tech Q1 2023 72952. 67319. 69029. 209300.
## 10 Tech Q2 2023 132511. 92360. 46537. 271407.
## 11 Tech Q3 2023 114974. 163541. 42600. 321115.
## 12 Tech Q4 2023 54695. 81079. 84751. 220525.
## 13 Speed Q1 2021 114201. 124978. 56891. 296070.
## 14 Speed Q2 2021 55393. 170537. 101008. 326938.
## 15 Speed Q3 2021 117534. 80479. 47351. 245365.
## 16 Speed Q4 2021 63231. 114318. 53936. 231486.
## 17 Speed Q1 2022 90856. 64648. 51327. 206832.
## 18 Speed Q2 2022 127823. 80929. 50100. 258853.
## 19 Speed Q3 2022 92629. 128247. 65873. 286749.
## 20 Speed Q4 2022 93628. 123767. 41367. 258762.
## 21 Speed Q1 2023 109554. 91860. 52643. 254056.
## 22 Speed Q2 2023 56288. 106295. 38850. 201433.
## 23 Speed Q3 2023 55336. 108447. 51040. 214822.
## 24 Speed Q4 2023 110499. 77886. 25332. 213716.
## 25 Maclr Q1 2021 114330. 61252. 44240. 219822.
## 26 Maclr Q2 2021 52048. 108249. 66052. 226348.
## 27 Maclr Q3 2021 82547. 112199. 95321. 290067.
## 28 Maclr Q4 2021 88720. 97200. 49054. 234974.
## 29 Maclr Q1 2022 60878. 114294. 34612. 209784.
## 30 Maclr Q2 2022 76411. 129880. 50996. 257286.
## 31 Maclr Q3 2022 121766. 71789. 60031. 253586.
## 32 Maclr Q4 2022 57241. 143620. 48755. 249616.
## 33 Maclr Q1 2023 144067. 86458. 58828. 289354.
## 34 Maclr Q2 2023 66585. 66363. 49833. 182781.
## 35 Maclr Q3 2023 58025. 89493. 32418. 179936.
## 36 Maclr Q4 2023 94109. 120419. 41572. 256100.
## 37 Dain Q1 2021 73285. 79141. 62189. 214615.
## 38 Dain Q2 2021 118274. 104071. 53933. 276278.
## 39 Dain Q3 2021 81358. 141023. 101902. 324283.
## 40 Dain Q4 2021 68008. 129744. 84692. 282444.
## 41 Dain Q1 2022 109980. 71892. 33210. 215083.
## 42 Dain Q2 2022 86083. 91393. 37635. 215110.
## 43 Dain Q3 2022 128536. 87550. 66957. 283043.
## 44 Dain Q4 2022 104447. 105628. 73960. 284034.
## 45 Dain Q1 2023 77457. 143409. 38354. 259219.
## 46 Dain Q2 2023 55170. 139965. 46134. 241270.
## 47 Dain Q3 2023 65167. 127152. 42307. 234625.
## 48 Dain Q4 2023 136867. 153258. 62151. 352276.
## # ℹ 2 more variables: Property_Plant_Equipment <dbl>, Total_Assets <dbl>print(balance_sheet_L_S)## # A tibble: 48 × 12
## Company Quarter Year Accounts_Payable Short_Term_Debt Current_Liabilities
## <chr> <chr> <dbl> <dbl> <dbl> <dbl>
## 1 Tech Q1 2021 64310. 82956. 147266.
## 2 Tech Q2 2021 34109. 64510. 98619.
## 3 Tech Q3 2021 52609. 114660. 167269.
## 4 Tech Q4 2021 47387. 107439. 154827.
## 5 Tech Q1 2022 47422. 130525. 177948.
## 6 Tech Q2 2022 51469. 70189. 121658.
## 7 Tech Q3 2022 68962. 93160. 162122.
## 8 Tech Q4 2022 92628. 98568. 191196.
## 9 Tech Q1 2023 56606. 90380. 146987.
## 10 Tech Q2 2023 43997. 82808. 126805.
## 11 Tech Q3 2023 62431. 128085. 190516.
## 12 Tech Q4 2023 64435. 125726. 190161.
## 13 Speed Q1 2021 64772. 74729. 139501.
## 14 Speed Q2 2021 78770. 192558. 271328.
## 15 Speed Q3 2021 46572. 84371. 130943.
## 16 Speed Q4 2021 62834. 92974. 155809.
## 17 Speed Q1 2022 38498. 119104. 157602.
## 18 Speed Q2 2022 43219. 98778. 141997.
## 19 Speed Q3 2022 94898. 192936. 287834.
## 20 Speed Q4 2022 49201. 85096. 134297.
## 21 Speed Q1 2023 58343. 77501. 135844.
## 22 Speed Q2 2023 44772. 100302. 145075.
## 23 Speed Q3 2023 62281. 121023. 183304.
## 24 Speed Q4 2023 28680. 97510. 126190.
## 25 Maclr Q1 2021 34330. 58318. 92649.
## 26 Maclr Q2 2021 42400. 97854. 140253.
## 27 Maclr Q3 2021 65110. 107651. 172761.
## 28 Maclr Q4 2021 58549. 121313. 179862.
## 29 Maclr Q1 2022 50651. 119400. 170050.
## 30 Maclr Q2 2022 85335. 88028. 173363.
## 31 Maclr Q3 2022 40485. 65364. 105849.
## 32 Maclr Q4 2022 69875. 87218. 157093.
## 33 Maclr Q1 2023 59586. 109525. 169111.
## 34 Maclr Q2 2023 41490. 122399. 163888.
## 35 Maclr Q3 2023 47917. 98455. 146372.
## 36 Maclr Q4 2023 63729. 134469. 198198.
## 37 Dain Q1 2021 57002. 113184. 170186.
## 38 Dain Q2 2021 39148. 65784. 104933.
## 39 Dain Q3 2021 85216. 113397. 198613.
## 40 Dain Q4 2021 50292. 106791. 157082.
## 41 Dain Q1 2022 28161. 102249. 130410.
## 42 Dain Q2 2022 27863. 96623. 124485.
## 43 Dain Q3 2022 43686. 82917. 126602.
## 44 Dain Q4 2022 91711. 173419. 265130.
## 45 Dain Q1 2023 64621. 85810. 150432.
## 46 Dain Q2 2023 76721. 160293. 237014.
## 47 Dain Q3 2023 44534. 144499. 189033.
## 48 Dain Q4 2023 44532. 149466. 193998.
## # ℹ 6 more variables: Long_Term_Debt <dbl>, Total_Liabilities <dbl>,
## # Common_Stock <dbl>, Retained_Earnings <dbl>, Shareholders_Equity <dbl>,
## # Total_Liabilities_and_Equity <dbl>Subsequently, I transformed the original database to enhance its readability and clarity. This transformation includes organizing the data in a structured format, making it easier to interpret and analyze. Here’s how the data now looks:
Tech <- read_excel("D:/Project/P_Adjusted2.xlsx", sheet = 1)## New names:
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13` Speed <- read_excel("D:/Project/P_Adjusted2.xlsx", sheet = 2)## New names:
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13` Maclr <- read_excel("D:/Project/P_Adjusted2.xlsx", sheet = 3)## New names:
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13` Dain <- read_excel("D:/Project/P_Adjusted2.xlsx", sheet = 4)## New names:
## • `` -> `...2`
## • `` -> `...3`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...6`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...9`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...12`
## • `` -> `...13`print(Tech)## # A tibble: 33 × 13
## `Income Statement` ...2 ...3 ...4 ...5 ...6 ...7 ...8 ...9 ...10
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023
## 2 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1
## 3 Revenue 6487… 5652… 7586… 6690… 8818… 5187… 6566… 9228… 6390…
## 4 COGS 3653… 2609… 4541… 3502… 4292… 2784… 3448… 4896… 3487…
## 5 Gross_Profit 2833… 3042… 3044… 3187… 4526… 2402… 3117… 4331… 2902…
## 6 Operating_Expenses 1895… 1277… 1722… 1439… 2566… 1219… 1337… 2507… 1789…
## 7 EBIT 93793 1765… 1322… 1748… 1959… 1183… 1780… 1823… 1113…
## 8 Interest_Expense 1851 4469 11493 3853 5038 8259 9580 6491 4776
## 9 EBT 91943 1720… 1207… 1709… 1908… 1100… 1684… 1758… 1065…
## 10 Tax_Expense 27583 51614 36222 51287 57266 33017 50529 52761 31966
## 11 Net_Income 64360 1204… 84517 1196… 1336… 77041 1179… 1231… 74587
## 12 Balance Sheet <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 13 Assets <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 14 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023
## 15 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1
## 16 Cash 1082… 74653 59313 1217… 55973 80702 88388 67934 72952
## 17 Accounts_Receivable 67301 57080 84926 1080… 1136… 1028… 1277… 1290… 67319
## 18 Inventory 60333 36565 59168 66411 48630 33238 61840 86907 69029
## 19 Current_Assets 2359… 1682… 2034… 2962… 2182… 2167… 2779… 2838… 2093…
## 20 Property_Plant_Equipme… 4434… 5110… 7229… 4294… 6787… 3160… 6128… 6418… 5527…
## 21 Total_Assets 6793… 6793… 9263… 7257… 8969… 5328… 8907… 9256… 7620…
## 22 Liability & Shareholde… <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 23 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023
## 24 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1
## 25 Accounts_Payable 64310 34109 52609 47387 47422 51469 68962 92628 56606
## 26 Short_Term_Debt 82956 64510 1146… 1074… 1305… 70189 93160 98568 90380
## 27 Current_Liabilities 1472… 98619 1672… 1548… 1779… 1216… 1621… 1911… 1469…
## 28 Long_Term_Debt 1608… 1272… 1625… 2018… 2091… 2021… 2023… 2848… 2510…
## 29 Total_Liabilities 3081… 2258… 3297… 3567… 3871… 3237… 3644… 4760… 3980…
## 30 Common_Stock 71442 79474 1718… 1067… 1773… 79501 90511 1284… 1259…
## 31 Retained_Earnings 2998… 3740… 4247… 2622… 3324… 1295… 4358… 3211… 2380…
## 32 Shareholders_Equity 3712… 4535… 5966… 3690… 5098… 2090… 5263… 4496… 3640…
## 33 Total_Liabilities_and_… 6793… 6793… 9263… 7257… 8969… 5328… 8907… 9256… 7620…
## # ℹ 3 more variables: ...11 <chr>, ...12 <chr>, ...13 <chr>print(Speed)## # A tibble: 33 × 13
## `Income Statement` ...2 ...3 ...4 ...5 ...6 ...7 ...8 ...9 ...10
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023
## 2 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1
## 3 Revenue 6572… 9806… 5514… 7870… 6410… 5633… 9946… 6580… 5749…
## 4 COGS 3336… 5760… 3145… 3473… 3088… 3100… 5021… 3449… 3306…
## 5 Gross_Profit 2833… 4046… 2368… 4396… 3322… 2532… 4925… 3130… 2442…
## 6 Operating_Expenses 1862… 1277… 1637… 2342… 1715… 1565… 2866… 1428… 1323…
## 7 EBIT 93793 2768… 73126 2053… 1606… 96688 2058… 1702… 1119…
## 8 Interest_Expense 7072 4469 12872 6518 3458 9235 7179 13524 2566
## 9 EBT 1302… 1588… 60254 1988… 1571… 87454 1987… 1567… 1093…
## 10 Tax_Expense 39080 47667 18076 59654 47148 26236 59616 47029 32806
## 11 Net_Income 91187 1112… 42178 1391… 1100… 61218 1391… 1097… 76548
## 12 Balance Sheet <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 13 Assets <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 14 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023
## 15 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1
## 16 Cash 1142… 55393 1175… 63231 90856 1278… 92629 93628 1095…
## 17 Accounts_Receivable 1249… 1705… 80479 1143… 64648 80929 1282… 1237… 91860
## 18 Inventory 56891 1010… 47351 53936 51327 50100 65873 41367 52643
## 19 Current_Assets 2960… 3269… 2453… 2314… 2068… 2588… 2867… 2587… 2540…
## 20 Property_Plant_Equipme… 6336… 7974… 3502… 7422… 4726… 5398… 6015… 5726… 5393…
## 21 Total_Assets 9296… 1124… 5956… 9737… 6795… 7986… 8883… 8314… 7933…
## 22 Liability & Shareholde… <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 23 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023
## 24 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1
## 25 Accounts_Payable 64772 78770 46572 62834 38498 43219 94898 49201 58343
## 26 Short_Term_Debt 74729 1925… 84371 92974 1191… 98778 1929… 85096 77501
## 27 Current_Liabilities 1395… 2713… 1309… 1558… 1576… 1419… 2878… 1342… 1358…
## 28 Long_Term_Debt 1628… 2611… 1127… 2402… 1707… 1967… 2675… 2211… 2138…
## 29 Total_Liabilities 3023… 5324… 2437… 3960… 3283… 3387… 5553… 3554… 3496…
## 30 Common_Stock 1012… 1350… 73998 1474… 86298 1475… 1414… 1506… 1569…
## 31 Retained_Earnings 5261… 4568… 2779… 4303… 2648… 3123… 1914… 3253… 2867…
## 32 Shareholders_Equity 6273… 5919… 3519… 5777… 3511… 4599… 3329… 4759… 4436…
## 33 Total_Liabilities_and_… 9296… 1124… 5956… 9737… 6795… 7986… 8883… 8314… 7933…
## # ℹ 3 more variables: ...11 <chr>, ...12 <chr>, ...13 <chr>print(Maclr)## # A tibble: 33 × 13
## `Income Statement` ...2 ...3 ...4 ...5 ...6 ...7 ...8 ...9 ...10
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023
## 2 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1
## 3 Revenue 5752… 5881… 8309… 6109… 6211… 8448… 5733… 8495… 8109…
## 4 COGS 3098… 3317… 4953… 3277… 2972… 4590… 3068… 3521… 4031…
## 5 Gross_Profit 2833… 2563… 3356… 2832… 3238… 3858… 2665… 4974… 4077…
## 6 Operating_Expenses 1552… 1277… 2310… 1744… 1737… 2269… 1537… 2185… 1947…
## 7 EBIT 93793 1285… 1045… 1087… 1501… 1589… 1127… 2788… 2130…
## 8 Interest_Expense 9862 4469 5103 4117 6447 13040 6570 7506 7960
## 9 EBT 1002… 1176… 99459 1046… 1436… 1458… 1061… 2713… 2050…
## 10 Tax_Expense 30070 35305 29838 31395 43106 43761 31845 81409 61521
## 11 Net_Income 70164 82378 69621 73254 1005… 1021… 74304 1899… 1435…
## 12 Balance Sheet <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 13 Assets <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 14 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023
## 15 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1
## 16 Cash 1143… 52048 82547 88720 1143… 52048 82547 88720 60878
## 17 Accounts_Receivable 61252 1082… 1121… 97200 61252 1082… 1121… 97200 1142…
## 18 Inventory 44240 66052 95321 49054 44240 66052 95321 49054 34612
## 19 Current_Assets 2198… 2263… 2900… 2349… 2198… 2263… 2900… 2349… 2097…
## 20 Property_Plant_Equipme… 3687… 3010… 4382… 4618… 3687… 3010… 4382… 4618… 3291…
## 21 Total_Assets 5886… 5273… 7283… 6968… 5886… 5273… 7283… 6968… 5389…
## 22 Liability & Shareholde… <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 23 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023
## 24 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1
## 25 Accounts_Payable 34330 42400 65110 58549 34330 42400 65110 58549 50651
## 26 Short_Term_Debt 58318 97854 1076… 1213… 58318 97854 1076… 1213… 1194…
## 27 Current_Liabilities 92649 1402… 1727… 1798… 92649 1402… 1727… 1798… 1700…
## 28 Long_Term_Debt 1975… 1977… 2984… 1232… 1975… 1977… 2984… 1232… 1898…
## 29 Total_Liabilities 2901… 3380… 4712… 3030… 2901… 3380… 4712… 3030… 3598…
## 30 Common_Stock 66111 71068 82858 97538 66111 71068 82858 97538 55473
## 31 Retained_Earnings 2322… 1182… 1742… 2961… 2322… 1182… 1742… 2961… 1235…
## 32 Shareholders_Equity 2984… 1893… 2570… 3937… 2984… 1893… 2570… 3937… 1790…
## 33 Total_Liabilities_and_… 5886… 5273… 7283… 6968… 5886… 5273… 7283… 6968… 5389…
## # ℹ 3 more variables: ...11 <chr>, ...12 <chr>, ...13 <chr>print(Dain)## # A tibble: 33 × 13
## `Income Statement` ...2 ...3 ...4 ...5 ...6 ...7 ...8 ...9 ...10
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023
## 2 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1
## 3 Revenue 5822… 6001… 9503… 9706… 5126… 5620… 6903… 8925… 7864…
## 4 COGS 3163… 3345… 5565… 4716… 2145… 2685… 4022… 5346… 3462…
## 5 Gross_Profit 2833… 2656… 3938… 4990… 2980… 2934… 2881… 3578… 4402…
## 6 Operating_Expenses 1740… 1277… 2722… 2439… 1457… 1369… 1470… 2096… 2197…
## 7 EBIT 93793 1378… 1215… 2550… 1522… 1565… 1410… 1482… 2204…
## 8 Interest_Expense 9789 4469 4735 19283 2081 8831 9876 7790 6859
## 9 EBT 82120 1390… 1168… 2357… 1502… 1477… 1311… 1404… 2136…
## 10 Tax_Expense 24636 41721 35040 70730 45061 44312 39358 42136 64083
## 11 Net_Income 57484 97348 81761 1650… 1051… 1033… 91836 98317 1495…
## 12 Balance Sheet <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 13 Assets <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 14 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023
## 15 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1
## 16 Cash 73285 1182… 81358 68008 1099… 86083 1285… 1044… 77457
## 17 Accounts_Receivable 79141 1040… 1410… 1297… 71892 91393 87550 1056… 1434…
## 18 Inventory 62189 53933 1019… 84692 33210 37635 66957 73960 38354
## 19 Current_Assets 2146… 2762… 3242… 2824… 2150… 2151… 2830… 2840… 2592…
## 20 Property_Plant_Equipme… 3023… 3417… 5841… 6957… 4114… 5470… 4828… 7129… 6157…
## 21 Total_Assets 5169… 6180… 9084… 9781… 6265… 7621… 7659… 9969… 8749…
## 22 Liability & Shareholde… <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA> <NA>
## 23 <NA> 2021 <NA> <NA> <NA> 2022 <NA> <NA> <NA> 2023
## 24 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1
## 25 Accounts_Payable 57002 39148 85216 50292 28161 27863 43686 91711 64621
## 26 Short_Term_Debt 1131… 65784 1133… 1067… 1022… 96623 82917 1734… 85810
## 27 Current_Liabilities 1701… 1049… 1986… 1570… 1304… 1244… 1266… 2651… 1504…
## 28 Long_Term_Debt 1428… 1836… 3037… 2536… 1169… 1725… 1927… 2608… 1686…
## 29 Total_Liabilities 3130… 2886… 5023… 4107… 2473… 2970… 3193… 5259… 3190…
## 30 Common_Stock 82810 1199… 1528… 1151… 88604 1285… 93567 1796… 1492…
## 31 Retained_Earnings 1210… 2094… 2531… 4523… 2905… 3365… 3529… 2913… 4066…
## 32 Shareholders_Equity 2039… 3293… 4060… 5674… 3791… 4650… 4465… 4710… 5559…
## 33 Total_Liabilities_and_… 5169… 6180… 9084… 9781… 6265… 7621… 7659… 9969… 8749…
## # ℹ 3 more variables: ...11 <chr>, ...12 <chr>, ...13 <chr>After refining and adjusting the values, we are now ready to proceed with the analysis.
In the file provided below, you will find three sheets:
DATA <- read_excel("D:/Project/Measures.xlsx", sheet = 1)
print(DATA)## # A tibble: 48 × 27
## Company Year `Current Ratio` `Quick Ratio` `Debt-To-equity` `Debt-To-Assets`
## <chr> <chr> <dbl> <dbl> <dbl> <dbl>
## 1 TECH 21-Q1 1.60 1.19 0.657 0.359
## 2 TECH 21-Q2 1.71 1.34 0.423 0.282
## 3 TECH 21-Q3 1.22 0.862 0.465 0.299
## 4 TECH 21-Q4 1.91 1.48 0.838 0.426
## 5 TECH 22-Q1 1.23 0.953 0.666 0.379
## 6 TECH 22-Q2 1.78 1.51 1.30 0.511
## 7 TECH 22-Q3 1.71 1.33 0.561 0.332
## 8 TECH 22-Q4 1.48 1.03 0.853 0.414
## 9 TECH 23-Q1 1.42 0.954 0.938 0.448
## 10 TECH 23-Q2 2.14 1.77 0.503 0.314
## 11 TECH 23-Q3 1.69 1.46 0.526 0.326
## 12 TECH 23-Q4 1.16 0.714 1.91 0.592
## 13 SPEED 21-Q1 2.12 1.71 0.379 0.256
## 14 SPEED 21-Q2 1.20 0.833 0.766 0.403
## 15 SPEED 21-Q3 1.87 1.51 0.560 0.331
## 16 SPEED 21-Q4 1.49 1.14 0.577 0.342
## 17 SPEED 22-Q1 1.31 0.987 0.826 0.427
## 18 SPEED 22-Q2 1.82 1.47 0.642 0.370
## 19 SPEED 22-Q3 0.996 0.767 1.38 0.518
## 20 SPEED 22-Q4 1.93 1.62 0.643 0.368
## 21 SPEED 23-Q1 1.87 1.48 0.657 0.367
## 22 SPEED 23-Q2 1.39 1.12 1.19 0.508
## 23 SPEED 23-Q3 1.17 0.894 0.495 0.311
## 24 SPEED 23-Q4 1.69 1.49 0.995 0.471
## 25 MACLR 21-Q1 2.37 1.90 0.857 0.435
## 26 MACLR 21-Q2 1.61 1.14 1.56 0.561
## 27 MACLR 21-Q3 1.68 1.13 1.58 0.558
## 28 MACLR 21-Q4 1.31 1.03 0.621 0.351
## 29 MACLR 22-Q1 2.37 1.90 0.857 0.435
## 30 MACLR 22-Q2 1.61 1.14 1.56 0.561
## 31 MACLR 22-Q3 1.68 1.13 1.58 0.558
## 32 MACLR 22-Q4 1.31 1.03 0.621 0.351
## 33 MACLR 23-Q1 1.23 1.03 1.73 0.574
## 34 MACLR 23-Q2 1.48 1.19 0.425 0.274
## 35 MACLR 23-Q3 2.40 1.83 0.495 0.314
## 36 MACLR 23-Q4 1.59 1.28 0.902 0.435
## 37 DAIN 21-Q1 1.26 0.896 1.26 0.495
## 38 DAIN 21-Q2 2.63 2.12 0.757 0.404
## 39 DAIN 21-Q3 1.63 1.12 1.03 0.459
## 40 DAIN 21-Q4 1.80 1.26 0.635 0.368
## 41 DAIN 22-Q1 1.65 1.39 0.578 0.350
## 42 DAIN 22-Q2 1.73 1.43 0.579 0.353
## 43 DAIN 22-Q3 2.24 1.71 0.617 0.360
## 44 DAIN 22-Q4 1.07 0.792 0.922 0.436
## 45 DAIN 23-Q1 1.72 1.47 0.458 0.291
## 46 DAIN 23-Q2 1.02 0.823 1.36 0.528
## 47 DAIN 23-Q3 1.24 1.02 0.717 0.395
## 48 DAIN 23-Q4 1.82 1.50 1.10 0.498
## # ℹ 21 more variables: `Debt-To-Captial` <dbl>, `Interest-Coverage` <dbl>,
## # `Recivables Turnover` <dbl>, DSO <dbl>, `Payables Turnover` <dbl>,
## # DPO <dbl>, `Inventory Turnover` <dbl>, DOH <dbl>,
## # `Cash Conversion Cycle` <dbl>, `Gross Profit Margin` <dbl>,
## # `EBIT Margin` <dbl>, `Tax Burden` <dbl>, `Interest Burden` <dbl>,
## # `Net Profit Margin` <dbl>, `Asset-Turnover` <dbl>,
## # `Return-On-Assets` <dbl>, `Equity-Multiplier` <dbl>, …Changes <- read_excel("D:/Project/Measures.xlsx", sheet = 2)
print(Changes)## # A tibble: 48 × 27
## Company `CHANGES %` `Current Ratio` `Quick Ratio` `Debt-To-equity`
## <chr> <chr> <dbl> <dbl> <dbl>
## 1 Tech 21-Q1 0 0 0
## 2 Tech 21-Q2/21-Q1 0.0653 0.120 -0.356
## 3 Tech 21-Q3/21-Q2 -0.287 -0.354 0.0990
## 4 Tech 21-Q4/21-Q3 0.574 0.722 0.804
## 5 Tech 22-Q1/21-Q4 -0.359 -0.358 -0.205
## 6 Tech 22-Q2/22-Q1 0.453 0.583 0.955
## 7 Tech 22-Q3/22-Q2 -0.0378 -0.116 -0.569
## 8 Tech 22-Q4/22-Q3 -0.134 -0.227 0.519
## 9 Tech 23-Q1/22-Q4 -0.0409 -0.0736 0.100
## 10 Tech 23-Q2/23-Q1 0.503 0.858 -0.463
## 11 Tech 23-Q3/23-Q2 -0.213 -0.176 0.0449
## 12 Tech 23-Q4/23-Q3 -0.312 -0.512 2.64
## 13 Speed 21-Q1 0 0 0
## 14 Speed 21-Q2/21-Q1 -0.432 -0.514 1.02
## 15 Speed 21-Q3/21-Q2 0.555 0.816 -0.269
## 16 Speed 21-Q4/21-Q3 -0.207 -0.246 0.0296
## 17 Speed 22-Q1/21-Q4 -0.117 -0.134 0.431
## 18 Speed 22-Q2/22-Q1 0.389 0.490 -0.222
## 19 Speed 22-Q3/22-Q2 -0.454 -0.478 1.15
## 20 Speed 22-Q4/22-Q3 0.934 1.11 -0.535
## 21 Speed 23-Q1/22-Q4 -0.0294 -0.0841 0.0206
## 22 Speed 23-Q2/23-Q1 -0.258 -0.244 0.811
## 23 Speed 23-Q3/23-Q2 -0.156 -0.203 -0.584
## 24 Speed 23-Q4/23-Q3 0.445 0.671 1.01
## 25 Maclr 21-Q1 0 0 0
## 26 Maclr 21-Q2/21-Q1 -0.320 -0.397 0.821
## 27 Maclr 21-Q3/21-Q2 0.0404 -0.0137 0.0118
## 28 Maclr 21-Q4/21-Q3 -0.222 -0.0830 -0.607
## 29 Maclr 22-Q1/21-Q4 0.816 0.833 0.381
## 30 Maclr 22-Q2/22-Q1 -0.320 -0.397 0.821
## 31 Maclr 22-Q3/22-Q2 0.0404 -0.0137 0.0118
## 32 Maclr 22-Q4/22-Q3 -0.222 -0.0830 -0.607
## 33 Maclr 23-Q1/22-Q4 -0.0557 -0.00344 1.78
## 34 Maclr 23-Q2/23-Q1 0.203 0.155 -0.754
## 35 Maclr 23-Q3/23-Q2 0.614 0.537 0.165
## 36 Maclr 23-Q4/23-Q3 -0.337 -0.301 0.824
## 37 Dain 21-Q1 0 0 0
## 38 Dain 21-Q2/21-Q1 1.09 1.37 -0.397
## 39 Dain 21-Q3/21-Q2 -0.380 -0.472 0.357
## 40 Dain 21-Q4/21-Q3 0.101 0.124 -0.382
## 41 Dain 22-Q1/21-Q4 -0.0827 0.108 -0.0896
## 42 Dain 22-Q2/22-Q1 0.0477 0.0223 0.000949
## 43 Dain 22-Q3/22-Q2 0.294 0.197 0.0668
## 44 Dain 22-Q4/22-Q3 -0.521 -0.536 0.493
## 45 Dain 23-Q1/22-Q4 0.608 0.853 -0.504
## 46 Dain 23-Q2/23-Q1 -0.409 -0.439 1.96
## 47 Dain 23-Q3/23-Q2 0.219 0.236 -0.471
## 48 Dain 23-Q4/23-Q3 0.463 0.470 0.540
## # ℹ 22 more variables: `Debt-To-Assets` <dbl>, `Debt-To-Captial` <dbl>,
## # `Interest-Coverage` <dbl>, `Recivables Turnover` <dbl>, DSO <dbl>,
## # `Payables Turnover` <dbl>, DPO <dbl>, `Inventory Turnover` <dbl>,
## # DOH <dbl>, `Cash Conversion Cycle` <dbl>, `Gross Profit Margin` <dbl>,
## # `EBIT Margin` <dbl>, `Tax Burden` <dbl>, `Interest Burden` <dbl>,
## # `Net Profit Margin` <dbl>, `Asset-Turnover` <dbl>,
## # `Return-On-Assets` <dbl>, `Equity-Multiplier` <dbl>, …Which Contain Four Main Measures
First: Financial_Health Measures
library(ggplot2)
library(plotly)##
## Attaching package: 'plotly'## The following object is masked from 'package:ggplot2':
##
## last_plot## The following object is masked from 'package:stats':
##
## filter## The following object is masked from 'package:graphics':
##
## layoutlibrary(dplyr)
library(tidyr)
# Define the data
quarters <- c('21-Q1', '21-Q2', '21-Q3', '21-Q4',
'22-Q1', '22-Q2', '22-Q3', '22-Q4',
'23-Q1', '23-Q2', '23-Q3', '23-Q4')
company_tech_gross <- c(44, 54, 40, 48, 51, 46, 47, 47, 45, 44, 55, 40)
company_speed_gross <- c(43, 41, 43, 56, 52, 45, 50, 48, 42, 58, 57, 56)
company_maclr_gross <- c(49, 44, 40, 46, 52, 46, 46, 59, 50, 60, 45, 52)
company_dain_gross <- c(49, 44, 41, 51, 58, 52, 42, 40, 56, 50, 51, 54)
company_tech_ebit <- c(14, 31, 17, 26, 22, 23, 27, 20, 17, 18, 31, 11)
company_speed_ebit <- c(14, 28, 13, 26, 25, 17, 21, 26, 19, 38, 33, 33)
company_maclr_ebit <- c(16, 22, 13, 18, 24, 19, 20, 33, 26, 38, 16, 30)
company_dain_ebit <- c(16, 23, 13, 26, 30, 28, 20, 17, 28, 29, 29, 29)
company_tech_net <- c(10, 21, 11, 18, 15, 15, 18, 13, 12, 13, 21, 7)
company_speed_net <- c(14, 11, 8, 18, 17, 11, 14, 17, 13, 26, 22, 22)
company_maclr_net <- c(12, 14, 8, 12, 16, 12, 13, 22, 18, 26, 11, 19)
company_dain_net <- c(10, 16, 9, 17, 21, 18, 13, 11, 19, 20, 20, 19)
data_gross <- data.frame(
Quarter = quarters,
Tech = company_tech_gross,
Speed = company_speed_gross,
Maclr = company_maclr_gross,
Dain = company_dain_gross,
Measure = "Gross_Profit_Margin"
)
data_ebit <- data.frame(
Quarter = quarters,
Tech = company_tech_ebit,
Speed = company_speed_ebit,
Maclr = company_maclr_ebit,
Dain = company_dain_ebit,
Measure = "EBIT_Margin"
)
data_net <- data.frame(
Quarter = quarters,
Tech = company_tech_net,
Speed = company_speed_net,
Maclr = company_maclr_net,
Dain = company_dain_net,
Measure = "Net_Profit_Margin"
)
data <- rbind(data_gross, data_ebit, data_net)
# Use pivot_longer to reshape the data
data <- data %>% pivot_longer(cols = c(Tech, Speed, Maclr, Dain), names_to = "Company", values_to = "Value")
# Filter data for Gross Profit Margin
data_gross_filtered <- data %>% filter(Measure == "Gross_Profit_Margin")
# Plot Gross Profit Margin
p_gross <- ggplot(data_gross_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Gross Profit Margin Comparison",
x = "Year-Quarter",
y = "Gross Profit Margin (%)",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_gross)# Filter data for EBIT Margin
data_ebit_filtered <- data %>% filter(Measure == "EBIT_Margin")
# Plot EBIT Margin
p_ebit <- ggplot(data_ebit_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "EBIT Margin Comparison",
x = "Year-Quarter",
y = "EBIT Margin (%)",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_ebit)# Filter data for Net Profit Margin
data_net_filtered <- data %>% filter(Measure == "Net_Profit_Margin")
# Plot Net Profit Margin
p_net <- ggplot(data_net_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Net Profit Margin Comparison",
x = "Year-Quarter",
y = "Net Profit Margin (%)",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_net)# Load required packages
library(ggplot2)
library(plotly)
library(dplyr)
library(tidyr)
# Define the data
quarters <- c('21-Q1', '21-Q2', '21-Q3', '21-Q4',
'22-Q1', '22-Q2', '22-Q3', '22-Q4',
'23-Q1', '23-Q2', '23-Q3', '23-Q4')
company_tech_roa <- c(9, 15, 10, 33, 19, 11, 13, 27, 10, 8, 20, 16)
company_speed_roa <- c(10, 13, 5, 29, 15, 7, 16, 26, 10, 21, 26, 45)
company_maclr_roa <- c(12, 16, 10, 11, 17, 19, 10, 27, 27, 16, 7, 17)
company_dain_roa <- c(11, 16, 9, 17, 17, 14, 12, 10, 17, 20, 18, 19)
company_tech_roe <- c(17, 27, 14, 32, 26, 37, 22, 27, 20, 17, 25, 25)
company_speed_roe <- c(15, 19, 12, 24, 31, 13, 42, 23, 17, 60, 31, 47)
company_maclr_roe <- c(24, 44, 27, 19, 34, 54, 29, 48, 80, 24, 11, 35)
company_dain_roe <- c(28, 30, 20, 29, 28, 22, 21, 21, 27, 51, 33, 43)
company_tech_oroa <- c(28, 26, 16, 21, 24, 17, 25, 20, 13, 15, 29, 9)
company_speed_oroa <- c(20, 27, 9, 26, 19, 13, 24, 20, 14, 35, 35, 23)
company_maclr_oroa <- c(32, 23, 17, 15, 23, 28, 18, 39, 34, 30, 9, 26)
company_dain_oroa <- c(36, 24, 16, 27, 19, 23, 18, 17, 24, 29, 25, 30)
data_roa <- data.frame(
Quarter = quarters,
Tech = company_tech_roa,
Speed = company_speed_roa,
Maclr = company_maclr_roa,
Dain = company_dain_roa,
Measure = "ROA"
)
data_roe <- data.frame(
Quarter = quarters,
Tech = company_tech_roe,
Speed = company_speed_roe,
Maclr = company_maclr_roe,
Dain = company_dain_roe,
Measure = "ROE"
)
data_oroa <- data.frame(
Quarter = quarters,
Tech = company_tech_oroa,
Speed = company_speed_oroa,
Maclr = company_maclr_oroa,
Dain = company_dain_oroa,
Measure = "Operating_ROA"
)
data <- rbind(data_roa, data_roe, data_oroa)
# Use pivot_longer to reshape the data
data <- data %>% pivot_longer(cols = c(Tech, Speed, Maclr, Dain), names_to = "Company", values_to = "Value")
# Filter data for ROA
data_roa_filtered <- data %>% filter(Measure == "ROA")
# Plot ROA
p_roa <- ggplot(data_roa_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "ROA Comparison",
x = "Year-Quarter",
y = "ROA (%)",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_roa)# Filter data for ROE
data_roe_filtered <- data %>% filter(Measure == "ROE")
# Plot ROE
p_roe <- ggplot(data_roe_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "ROE Comparison",
x = "Year-Quarter",
y = "ROE (%)",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_roe)# Filter data for Operating ROA
data_oroa_filtered <- data %>% filter(Measure == "Operating_ROA")
# Plot Operating ROA
p_oroa <- ggplot(data_oroa_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Operating ROA Comparison",
x = "Year-Quarter",
y = "Operating ROA (%)",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_oroa)Second: Solvency Measures
library(ggplot2)
library(plotly)
library(dplyr)
library(tidyr)
# Define the data
quarters <- c('21-Q1', '21-Q2', '21-Q3', '21-Q4',
'22-Q1', '22-Q2', '22-Q3', '22-Q4',
'23-Q1', '23-Q2', '23-Q3', '23-Q4')
company_tech_debt_to_assets <- c(36, 28, 30, 43, 38, 51, 33, 41, 45, 31, 33, 59)
company_speed_debt_to_assets <- c(26, 40, 33, 34, 43, 37, 52, 37, 37, 51, 31, 47)
company_maclr_debt_to_assets <- c(43, 56, 56, 35, 43, 56, 56, 35, 57, 27, 31, 43)
company_dain_debt_to_assets <- c(50, 40, 46, 37, 35, 35, 36, 44, 29, 53, 39, 50)
company_tech_debt_to_equity <- c(66, 42, 46, 84, 67, 130, 56, 85, 94, 50, 53, 191)
company_speed_debt_to_equity <- c(38, 77, 56, 58, 83, 64, 138, 64, 66, 119, 50, 99)
company_maclr_debt_to_equity <- c(86, 156, 158, 62, 86, 156, 158, 62, 173, 42, 49, 90)
company_dain_debt_to_equity <- c(126, 76, 103, 64, 58, 58, 62, 92, 46, 136, 72, 110)
company_tech_debt_to_capital <- c(40, 30, 32, 46, 40, 57, 36, 46, 48, 33, 34, 66)
company_speed_debt_to_capital <- c(27, 43, 36, 37, 45, 39, 58, 39, 40, 54, 33, 50)
company_maclr_debt_to_capital <- c(46, 61, 61, 38, 46, 61, 61, 38, 63, 30, 33, 47)
company_dain_debt_to_capital <- c(56, 43, 51, 39, 37, 37, 38, 48, 31, 58, 42, 52)
data_debt_to_assets <- data.frame(
Quarter = quarters,
Tech = company_tech_debt_to_assets,
Speed = company_speed_debt_to_assets,
Maclr = company_maclr_debt_to_assets,
Dain = company_dain_debt_to_assets,
Measure = "Debt_To_Assets"
)
data_debt_to_equity <- data.frame(
Quarter = quarters,
Tech = company_tech_debt_to_equity,
Speed = company_speed_debt_to_equity,
Maclr = company_maclr_debt_to_equity,
Dain = company_dain_debt_to_equity,
Measure = "Debt_To_Equity"
)
data_debt_to_capital <- data.frame(
Quarter = quarters,
Tech = company_tech_debt_to_capital,
Speed = company_speed_debt_to_capital,
Maclr = company_maclr_debt_to_capital,
Dain = company_dain_debt_to_capital,
Measure = "Debt_To_Capital"
)
data <- bind_rows(data_debt_to_assets, data_debt_to_equity, data_debt_to_capital)
data <- data %>% pivot_longer(cols = c(Tech, Speed, Maclr, Dain), names_to = "Company", values_to = "Value")
# Filter data for Debt-To-Assets
data_debt_to_assets_filtered <- data %>% filter(Measure == "Debt_To_Assets")
# Plot Debt-To-Assets
p_debt_to_assets <- ggplot(data_debt_to_assets_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Debt-To-Assets Comparison",
x = "Year-Quarter",
y = "Debt-To-Assets (%)",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_debt_to_assets)# Filter data for Debt-To-Equity
data_debt_to_equity_filtered <- data %>% filter(Measure == "Debt_To_Equity")
# Plot Debt-To-Equity
p_debt_to_equity <- ggplot(data_debt_to_equity_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Debt-To-Equity Comparison",
x = "Year-Quarter",
y = "Debt-To-Equity (%)",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_debt_to_equity)# Filter data for Debt-To-Capital
data_debt_to_capital_filtered <- data %>% filter(Measure == "Debt_To_Capital")
# Plot Debt-To-Capital
p_debt_to_capital <- ggplot(data_debt_to_capital_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Debt-To-Capital Comparison",
x = "Year-Quarter",
y = "Debt-To-Capital (%)",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_debt_to_capital)Third: Activity Measures
library(ggplot2)
library(plotly)
library(dplyr)
library(tidyr)
# Define the data
quarters <- c('21-Q1', '21-Q2', '21-Q3', '21-Q4',
'22-Q1', '22-Q2', '22-Q3', '22-Q4',
'23-Q1', '23-Q2', '23-Q3', '23-Q4')
# Data for each measure
company_tech_receivable <- c(19.28, 9.09, 10.68, 7.02, 5.89, 8.58, 4.06, 5.09, 13.71, 6.92, 3.64, 10.73)
company_speed_receivable <- c(10.52, 6.64, 4.39, 4.82, 12.17, 7.92, 4.39, 8.04, 7.16, 5.41, 6.24, 11.57)
company_maclr_receivable <- c(18.78, 6.94, 7.54, 8.55, 9.97, 5.74, 7.53, 5.90, 7.43, 6.24, 8.84, 3.66)
company_dain_receivable <- c(14.71, 6.55, 7.75, 7.32, 13.50, 5.61, 6.42, 6.54, 6.22, 5.62, 7.21, 4.86)
company_tech_payable <- c(11.36, 5.30, 10.47, 7.01, 9.05, 5.63, 5.73, 7.80, 12.32, 15.27, 7.33, 6.88)
company_speed_payable <- c(10.30, 8.03, 5.02, 6.35, 6.10, 7.59, 7.27, 4.50, 11.34, 12.63, 7.23, 5.08)
company_maclr_payable <- c(18.05, 8.65, 9.21, 5.30, 6.40, 11.97, 5.71, 6.08, 15.92, 6.01, 4.62, 6.31)
company_dain_payable <- c(11.10, 6.96, 8.95, 6.96, 5.47, 9.59, 11.25, 9.87, 10.72, 11.89, 5.98, 9.08)
company_tech_inventory <- c(12.11, 5.39, 9.49, 5.58, 7.46, 6.80, 7.25, 6.58, 4.47, 5.81, 8.76, 6.85)
company_speed_inventory <- c(11.73, 7.30, 4.24, 6.86, 5.87, 6.11, 8.66, 6.43, 7.04, 6.18, 8.61, 6.05)
company_maclr_inventory <- c(14.01, 6.02, 6.14, 4.54, 6.37, 8.32, 3.80, 4.88, 9.64, 5.99, 5.24, 6.41)
company_dain_inventory <- c(10.17, 5.76, 7.14, 5.06, 3.64, 7.58, 7.69, 7.59, 6.17, 10.79, 8.19, 7.74)
company_tech_cash <- c(16.95, 39.09, 37.78, 65.33, 70.60, 31.40, 76.44, 80.36, 78.61, 91.63, 92.15, 34.22)
company_speed_cash <- c(30.39, 59.54, 96.43, 71.40, 32.30, 57.68, 75.05, 21.08, 70.64, 97.63, 50.35, 20.02)
company_maclr_cash <- c(25.27, 71.06, 68.26, 54.23, 36.85, 76.96, 80.50, 76.71, 64.05, 58.65, 32.01, 98.85)
company_dain_cash <- c(27.80, 66.60, 57.39, 69.60, 60.59, 75.15, 71.85, 66.98, 83.79, 68.07, 34.10, 82.05)
data_receivable <- data.frame(
Quarter = quarters,
Company = rep(c('Tech', 'Speed', 'Maclr', 'Dain'), each = length(quarters)),
Value = c(company_tech_receivable, company_speed_receivable, company_maclr_receivable, company_dain_receivable),
Measure = 'Receivable Turnover'
)
data_payable <- data.frame(
Quarter = quarters,
Company = rep(c('Tech', 'Speed', 'Maclr', 'Dain'), each = length(quarters)),
Value = c(company_tech_payable, company_speed_payable, company_maclr_payable, company_dain_payable),
Measure = 'Payable Turnover'
)
data_inventory <- data.frame(
Quarter = quarters,
Company = rep(c('Tech', 'Speed', 'Maclr', 'Dain'), each = length(quarters)),
Value = c(company_tech_inventory, company_speed_inventory, company_maclr_inventory, company_dain_inventory),
Measure = 'Inventory Turnover'
)
data_cash <- data.frame(
Quarter = quarters,
Company = rep(c('Tech', 'Speed', 'Maclr', 'Dain'), each = length(quarters)),
Value = c(company_tech_cash, company_speed_cash, company_maclr_cash, company_dain_cash),
Measure = 'Cash Conversion Cycle'
)
data <- bind_rows(data_receivable, data_payable, data_inventory, data_cash)
# Filter data for Receivable Turnover
data_receivable_filtered <- data %>% filter(Measure == "Receivable Turnover")
# Plot Receivable Turnover
p_receivable <- ggplot(data_receivable_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Receivable Turnover Comparison",
x = "Year-Quarter",
y = "Receivable Turnover",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_receivable)# Filter data for Payable Turnover
data_payable_filtered <- data %>% filter(Measure == "Payable Turnover")
# Plot Payable Turnover
p_payable <- ggplot(data_payable_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Payable Turnover Comparison",
x = "Year-Quarter",
y = "Payable Turnover",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_payable)# Filter data for Inventory Turnover
data_inventory_filtered <- data %>% filter(Measure == "Inventory Turnover")
# Plot Inventory Turnover
p_inventory <- ggplot(data_inventory_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Inventory Turnover Comparison",
x = "Year-Quarter",
y = "Inventory Turnover",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_inventory)# Filter data for Cash Conversion Cycle
data_cash_filtered <- data %>% filter(Measure == "Cash Conversion Cycle")
# Plot Cash Conversion Cycle
p_cash <- ggplot(data_cash_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Cash Conversion Cycle Comparison",
x = "Year-Quarter",
y = "Cash Conversion Cycle",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_cash)Finally: Liquidity Measures
library(ggplot2)
library(dplyr)
library(tidyr)
# Define the data
quarters <- c('21-Q1', '21-Q2', '21-Q3', '21-Q4',
'22-Q1', '22-Q2', '22-Q3', '22-Q4',
'23-Q1', '23-Q2', '23-Q3', '23-Q4')
# Current Ratio data
current_ratio_tech <- c(1.60, 1.71, 1.22, 1.91, 1.23, 1.78, 1.71, 1.48, 1.42, 2.14, 1.69, 1.16)
current_ratio_speed <- c(2.12, 1.20, 1.87, 1.49, 1.31, 1.82, 1.00, 1.93, 1.87, 1.39, 1.17, 1.69)
current_ratio_maclr <- c(2.37, 1.61, 1.68, 1.31, 2.37, 1.61, 1.68, 1.31, 1.23, 1.48, 2.40, 1.59)
current_ratio_dain <- c(1.26, 2.63, 1.63, 1.80, 1.65, 1.73, 2.24, 1.07, 1.72, 1.02, 1.24, 1.82)
# Quick Ratio data
quick_ratio_tech <- c(1.19, 1.34, 0.86, 1.48, 0.95, 1.51, 1.33, 1.03, 0.95, 1.77, 1.46, 0.71)
quick_ratio_speed <- c(1.71, 0.83, 1.51, 1.14, 0.99, 1.47, 0.77, 1.62, 1.48, 1.12, 0.89, 1.49)
quick_ratio_maclr <- c(1.90, 1.14, 1.13, 1.03, 1.90, 1.14, 1.13, 1.03, 1.03, 1.19, 1.83, 1.28)
quick_ratio_dain <- c(0.90, 2.12, 1.12, 1.26, 1.39, 1.43, 1.71, 0.79, 1.47, 0.82, 1.02, 1.50)
data_current <- data.frame(
Quarter = rep(quarters, 4),
Company = rep(c('Tech', 'Speed', 'Maclr', 'Dain'), each = length(quarters)),
Value = c(current_ratio_tech, current_ratio_speed, current_ratio_maclr, current_ratio_dain),
Measure = 'Current Ratio'
)
data_quick <- data.frame(
Quarter = rep(quarters, 4),
Company = rep(c('Tech', 'Speed', 'Maclr', 'Dain'), each = length(quarters)),
Value = c(quick_ratio_tech, quick_ratio_speed, quick_ratio_maclr, quick_ratio_dain),
Measure = 'Quick Ratio'
)
data <- bind_rows(data_current, data_quick)
# Filter data for Current Ratio
data_current_filtered <- data %>% filter(Measure == "Current Ratio")
# Plot Current Ratio
p_current <- ggplot(data_current_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Current Ratio Comparison",
x = "Year-Quarter",
y = "Current Ratio",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_current)# Filter data for Quick Ratio
data_quick_filtered <- data %>% filter(Measure == "Quick Ratio")
# Plot Quick Ratio
p_quick <- ggplot(data_quick_filtered, aes(x = Quarter, y = Value, fill = Company)) +
geom_bar(stat = "identity", position = "dodge") +
labs(title = "Quick Ratio Comparison",
x = "Year-Quarter",
y = "Quick Ratio",
fill = "Company") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
ggplotly(p_quick)Speed: Upon reviewing its data, it is evident that this company is the dominant player in the commercial sector. This dominance has allowed it to impose strict controls on Accounts Receivable, enabling the company to collect its funds promptly at the end of each quarter (“For example, if a customer purchases on account for $100,000 at the beginning of Q1 2021, they have until the end of Q1 2022 to settle the amount due”). This practice contributes to fluctuations in the Cash Conversion Cycle each year. Regarding the company’s debt, the Solvency Ratios indicate that the company leverages its market power and dominance to increase its borrowing capacity. This has significantly impacted the Net Profit Margin, as the higher level of debt has effectively reduced the company’s tax burden. In terms of profitability, the key indicators, ROE (Return on Equity) and OROA (Operating Return on Operating Assets), show positive performance for two reasons. Firstly, OROA remains strong because the company has maintained stable inventory turnover (meaning the period in which the inventory is quickly sold). Secondly, ROE has remained stable due to the improvement and consistency in the Dupont Analysis.
Maclr: Similar to the previous company, Maclr has comparable Margin rates, which is expected for all companies in this sector due to the above-average economic conditions and demand. In contrast to the previous company, Maclr does not impose any restrictions on Accounts Receivables. This is evident from the low Receivable Turnover over the years, which has significantly impacted the Cash Conversion Cycle by extending it. This extension is detrimental to any commercial company aiming to shorten the cycle for quick cash collection and inventory purchases. Consequently, this has also negatively affected the Activity Ratios, showing a decline over the years. Regarding Solvency, despite the cash flow issue, the company’s Net Profit Margin remains stable and does not decline. This is because the company heavily relies on external debt rather than operating cash flow and liquidity. Although this strategy has reduced taxes and increased net income, it places the company at high risk of bankruptcy if it fails to meet its debt obligations. This issue is further highlighted by the declining Profitability Ratios over the coming years, confirming the problem of reduced operating efficiency. Additionally, the declining inventory turnover over the years indicates that the company is struggling with its operational systems.
Dain: Regarding Margins, there are no issues, and the indicators are normal, similar to other companies in the same sector. However, Dain faces a problem similar to Maclr, as it does not appear to impose any restrictions on Accounts Receivables. The consequences of this have already been discussed in the case of Maclr. One significant difference is that Dain has a high inventory turnover, indicating it sells its products quickly, almost surpassing the dominant company, Speed. This efficiency has also helped Dain reduce its Cash Conversion Cycle. Additionally, Dain has managed to extend its Payable Turnover without adversely affecting its operations over the years. Therefore, its only issue lies in the Receivable Turnover. Regarding debt, Dain is similar to Maclr, except for the bankruptcy risk. Dain has sufficient liquidity from its operating activities to manage its debt obligations, especially short-term debts.
Tech: Unlike the other companies, Tech shows a decrease in Margin Ratios, which is unusual compared to the surrounding companies. Another issue is the lack of restrictions on Accounts Receivables, despite stable inventory turnover rates and an increase in Payable Turnover. The lack of controls on Accounts Receivables has significantly extended the Cash Conversion Cycle due to the decrease in Receivable Turnover Ratio. This situation poses a greater risk to the company, particularly if bad debt and the Allowance for Doubtful Accounts increase, which could severely weaken its liquidity ratios. Although the company does not rely on debt to the same extent as other companies, this will affect the stability of its operating systems. The ROA indicator is stable but tends to decline in the coming years. The OROA has decreased significantly due to the company’s deteriorating ability to increase sales and another reason that will be mentioned later. Now, addressing the crucial point, the company has declining Margins despite the surrounding companies not experiencing similar issues. If we look at the Gross Profit Margin, it is lower compared to other companies. This could be due to one of two reasons: either the company is not well-known and thus sells less, which is unlikely since the data shows that all four companies have nearly equal profits, or the second and more likely reason is that Tech’s product pricing is higher than that of other companies. Upon investigating, it was found that Tech has higher Costs of Goods Sold compared to the other companies. Regarding the EBIT Margin (Earnings Before Interest and Tax), it has been low over the years, also due to higher Operating Expenses than the other companies. Ultimately, this has resulted in a lower Net Profit Margin. Additionally, the lower debt levels compared to other companies have led to higher taxes for Tech compared to its peers.
->Now that we have reviewed each company, summarizing their advantages and disadvantages, let’s discuss the solutions that should be presented to Tech, in order to make it competitive with Speed and other Companies.
Cost Analysis and Value Engineering: The first step Tech should take is to thoroughly examine its costs by implementing value engineering techniques. This involves identifying and eliminating all non-value-added activities to reduce overall costs. By doing so, the company can lower its product prices, thereby increasing its Gross Profit Margin and enhancing its inventory turnover.
Implementing Appropriate Controls on Accounts Receivables: After reducing prices, the company should introduce suitable controls on Accounts Receivables that align with its market position. This strategy will help increase the Receivable Turnover, thereby shortening the Cash Conversion Cycle. Consequently, this will improve liquidity and boost operating activities.
Optimizing Debt-to-Equity Ratio: Once the company stabilizes and its market value increases, it can leverage the third crucial factor: optimizing its Debt-to-Equity ratio. By aligning its debt levels with its financial position and liquidity at that time, the company can increase its Net Profit Margin and benefit from a reduction in taxes.
Implementing these strategies will enhance Tech’s competitiveness with Speed and other Companies and improve its overall financial performance.
Click here to view the Companies Performance Report.